package com.zx.jhpt.excel;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;

import com.zx.framework.core.env.Environment;
import com.zx.framework.core.exception.ServiceException;
import com.zx.framework.core.util.FileUtil;
import com.zx.framework.core.util.FreemarkerUtil;
import com.zx.framework.core.util.PinyinUtil;
import com.zx.framework.core.util.StringUtil;
import com.zx.framework.core.util.gson.GsonUtil;
import com.zx.jhpt.excel.vo.Column;
import com.zx.jhpt.excel.vo.Risk;
import com.zx.jhpt.util.ExcelUtilV2;

/**
 * 用于处理excel及报表部分的信息
 * @author Administrator
 *
 */
public class Engine {
	
	private static final Logger LOGGER = Logger.getLogger(Engine.class);
		/**
	 * 保存每个risk对象，key为风险点的编号
	 */
	private static Map<String, Risk> riskMap = new HashMap<String, Risk>();
	
	/**
	 * 缓存risk对象
	 * @param risk
	 */
	public static void putRisk(Risk risk){
		riskMap.put(risk.getFxid(), risk);
	}
	/**
	 * 获取缓存对象
	 * @param fxid
	 * @return
	 */
	public static Risk getRisk(String fxid){
		Risk risk = riskMap.get(fxid);
		if(risk == null){
			try {
				risk = getRiskFromPath(Environment.WEB_INF_REALPATH+File.separator+"tpl"+File.separator+fxid+".fxjz");
				putRisk(risk);
			} catch (IOException e) {
				LOGGER.error("获取风险点描述文件失败:"+e.getMessage());
				throw new ServiceException("获取风险点描述文件失败:"+e.getMessage());
			}
		}
		return risk;
	}
	
	/**
	 * 根据json获取结构
	 * @param json
	 * @return
	 */
	public static Risk getRiskFromJson(String json){
		Risk risk = GsonUtil.json2Bean(json, Risk.class);
		List<Column> cls = new ArrayList<>();
		//处理默认归属编码
		if(StringUtil.isNotEmpty(risk.getDefGsbm())){
			Column c = new Column();
			c.setId("GSBM");
			c.setName("归属编码");
			cls.add(c);
		}
		for (Column column : risk.getColumns()) {
			cls.add(column);
		}
		risk.setColumns(cls);
		getDbColumnName(risk.getColumns());
		risk.setTbName(risk.getFxid().replaceAll("-", "_"));
		return risk;
	}
	/**
	 * 处理私有列的信息
	 * @param columns
	 */
	public static void getDbColumnName(List<Column> columns){
		for (Column column : columns) {
			if(StringUtil.isEmpty(column.getId())){
				//私有列将从excel中读取
				column.setId(PinyinUtil.strGetHeadByString(column.getName()));
			}
		}
	}
	/**
	 * 拼接数据插入语句
	 * @param risk
	 * @return
	 */
	public static String getSql(Risk risk){
		StringBuilder sb = new StringBuilder();
		sb.append("insert into ");
		sb.append(risk.getTbName());
		sb.append("(");
		List<Column> columns = risk.getColumns();
		StringBuilder col = new StringBuilder();
		StringBuilder pos = new StringBuilder();
		
		for (Column column : columns) {
			col.append(column.getId());
			col.append(",");
			pos.append("?,");
		}
		col.append("ID,");
		col.append("FXID,");
		col.append("CYCLE");
		
		pos.append("?,");
		pos.append("?,");
		pos.append("?");
		
		sb.append(col);
		sb.append(")");
		sb.append(" values (");
		sb.append(pos);
		sb.append(")");
		return sb.toString();
	}
	/**
	 * 根据文件获取描述结构
	 * @param file
	 * @return
	 * @throws IOException
	 */
	public static Risk getRiskFromFile(File file) throws IOException{
		String json = FileUtil.readFileToString(file,"utf-8");
		return getRiskFromJson(json);
	}
	/**
	 * 根据路径获取描述结构
	 * @param path
	 * @return
	 * @throws IOException
	 */
	public static Risk getRiskFromPath(String path) throws IOException{
		return getRiskFromFile(new File(path));
	}
	
	/**
	 * 获取生成表格的sql
	 * @param risk
	 * @return
	 */
	public static String createTable(Risk risk){
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("risk",risk);
		return FreemarkerUtil.getInstance().executeTemplate(map, "tpl/create.tpl");
				
	}
	
	/**
	 * 获取excel中的数据
	 * @param path
	 * @return
	 */
	public static List<Object[]> getData(Risk risk,String path){
		ExcelUtilV2 excelUtil = new ExcelUtilV2();
		excelUtil.setStartReadPos(risk.getStartRow());
		List<Object[]> rs = new ArrayList<Object[]>();
		int colNUm = StringUtil.isEmpty(risk.getDefGsbm())?risk.getColumns().size():risk.getColumns().size()-1;
		try {
			List<Row> rows = excelUtil.readExcel(path);
			for (Row row : rows) {
				int length = colNUm;
				//返回行数据的偏移量，如果有默认部门，则所有excel列数据
				int plus = 0;
				//如果含有默认归属部门
				if(StringUtil.isNotEmpty(risk.getDefGsbm())){
					length = length +1;
					plus = 1;
				}
				Object[] temp = new Object[length];
				if(StringUtil.isNotEmpty(risk.getDefGsbm())){
					temp[0] = risk.getDefGsbm();
				}
				
				for (int j = 0; j < colNUm; j++) {
					 String value = excelUtil.getCellValue(row.getCell(j));
					 temp[j+plus] = value;
				}
				rs.add(temp);
			}
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	public static void main(String[] args) {
		try {
			Risk r = getRiskFromPath("e:/test.json");
			for (Column c : r.getColumns()) {
				System.out.println(GsonUtil.object2Json(c));
			}
			List rs = getData(r,"E:/demo2/SJZYZFXFK-1-1_一人多账号.xlsx");
			for (Object object : rs) {
				System.out.println(object);
			}
			System.out.println(getSql(r));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 获取员工id的列的序号
	 * @param risk
	 * @return
	 */
	public static int getAssignIndex(Risk risk){
		int rs = -1;
		
		for (int i = 0; i < risk.getColumns().size(); i++) {
			Column c = risk.getColumns().get(i);
			if(c.getName().equals(risk.getAssign())){
				rs = i;
				break;
			}
		}
		return rs;
	}
}
